﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SZSLSystem.Model;
using SZSLSystem.Common;
using SZSLSystem.DBUtility;

namespace SZSLSystem.DAL
{   
    /// <summary>
    /// 数据访问层：组织机构
    /// </summary>
    public partial class Organization
    {
        public Organization()
        {

        }

        #region  Method

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(Guid Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Organization");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier)};
            parameters[0].Value = Id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 返回数据总数(分页用到)
        /// </summary>
        public int GetCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) as H ");
            strSql.Append(" from Organization ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString()));
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SZSLSystem.Model.Organization model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Organization( ");
            strSql.Append("[ID],[OrganizationName],[ParentID],[ClassLayer],[Description],[Sort],[AddDate])");
            strSql.Append("values (@ID,@OrganizationName,@ParentID,@ClassLayer,@Description,@Sort,@AddDate)");
            SqlParameter[] parameters = {
					new SqlParameter ("@ID",SqlDbType.UniqueIdentifier),
					new SqlParameter ("@OrganizationName",SqlDbType.VarChar,50),
					new SqlParameter ("@ParentID",SqlDbType.UniqueIdentifier),
                    new SqlParameter ("@ClassLayer",SqlDbType.Int,4),
					new SqlParameter ("@Description",SqlDbType.VarChar,500),
					new SqlParameter ("@Sort",SqlDbType.Int,4),
                    new SqlParameter ("@AddDate",SqlDbType.DateTime)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.OrganizationName;
            parameters[2].Value = model.ParentID;
            parameters[3].Value = model.ClassLayer;
            parameters[4].Value = model.Description;
            parameters[5].Value = model.Sort;
            parameters[6].Value = model.AddDate;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(SZSLSystem.Model.Organization model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Organization set ");
            strSql.Append("[OrganizationName]=@OrganizationName,");
            strSql.Append("[ParentID]=@ParentID,");
            strSql.Append("[ClassLayer]=@ClassLayer,");
            strSql.Append("[Description]=@Description,");
            strSql.Append("[Sort]=@Sort");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter ("@ID",SqlDbType.UniqueIdentifier),
					new SqlParameter ("@OrganizationName",SqlDbType.VarChar,50),
					new SqlParameter ("@ParentID",SqlDbType.UniqueIdentifier),
                    new SqlParameter ("@ClassLayer",SqlDbType.Int,4),
					new SqlParameter ("@Description",SqlDbType.VarChar,500),
					new SqlParameter ("@Sort",SqlDbType.Int,4),
                    new SqlParameter ("@AddDate",SqlDbType.DateTime)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.OrganizationName;
            parameters[2].Value = model.ParentID;
            parameters[3].Value = model.ClassLayer;
            parameters[4].Value = model.Description;
            parameters[5].Value = model.Sort;
            parameters[6].Value = model.AddDate;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除该栏目分类及所有属下分类数据
        /// </summary>
        public bool Delete(Guid Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete Organization ");
            strSql.Append(" where ID = @ID");
            strSql.Append(" or ParentID = @ParentID");
            SqlParameter[] parameters = {
                    new SqlParameter("@ID", SqlDbType.UniqueIdentifier),
                    new SqlParameter("@ParentID", SqlDbType.UniqueIdentifier)};
            parameters[0].Value = Id;
            parameters[1].Value = Id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public SZSLSystem.Model.Organization GetModel(Guid Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from Organization ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier)
            };
            parameters[0].Value = Id;

            SZSLSystem.Model.Organization model = new SZSLSystem.Model.Organization();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow row = ds.Tables[0].Rows[0];
                if (row["ID"].ToString() != "")
                {
                    model.ID = (Guid)row["ID"];
                }
                model.OrganizationName = row["OrganizationName"].ToString();
                model.ClassLayer = int.Parse(row["ClassLayer"].ToString());
                if (row["ParentID"].ToString() != "")
                {
                    model.ParentID = (Guid)row["ParentID"];
                }
                model.Description = row["Description"].ToString();
                if (row["Sort"].ToString() != "")
                {
                    model.Sort = int.Parse(row["Sort"].ToString());
                }
                model.AddDate = DateTime.Parse(row["AddDate"].ToString());
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到所有商品类别
        /// </summary>
        /// <returns></returns>
        public DataTable GetList(Guid ParID)
        {
            DataTable data = new DataTable();
            //创建列
            DataColumn ID = new DataColumn("ID", typeof(Guid));
            DataColumn OrganizationName = new DataColumn("OrganizationName", typeof(string));
            DataColumn ClassLayer = new DataColumn("ClassLayer", typeof(int));
            DataColumn ParentID = new DataColumn("ParentID", typeof(Guid));
            DataColumn Description = new DataColumn("Description", typeof(string));
            DataColumn Sort = new DataColumn("Sort", typeof(int));
            DataColumn AddDate = new DataColumn("AddDate", typeof(DateTime));
            //添加列
            data.Columns.Add(ID);
            data.Columns.Add(OrganizationName);
            data.Columns.Add(ClassLayer);
            data.Columns.Add(ParentID);
            data.Columns.Add(Description);
            data.Columns.Add(Sort);
            data.Columns.Add(AddDate);
            SZSLSystem.Model.Organization model = GetModel(ParID);
            //添加一行数据
            if (model != null)
            {
                DataRow row = data.NewRow();
                row[0] = model.ID;
                row[1] = model.OrganizationName;
                row[2] = model.ClassLayer;
                row[3] = model.ParentID;
                row[4] = model.Description;
                row[5] = model.Sort;
                row[6] = model.AddDate;
                data.Rows.Add(row);
                GetChild(data, ParID);
            }
            return data;
        }

        /// <summary>
        /// 取得该类别下所有类别
        /// </summary>
        /// <param name="data">DATATABLE名</param>
        /// <param name="ParID">父ID</param>
        private void GetChild(DataTable data, Guid ParID)
        {
            StringBuilder str = new StringBuilder();
            str.Append("select * from Organization");
            str.Append(" where ParentID=@ParentID ");
            str.Append(" order by Sort asc");
            SqlParameter[] parameters = {
					new SqlParameter("@ParentID", SqlDbType.UniqueIdentifier)
            };
            parameters[0].Value = ParID;
            DataSet ds = DbHelperSQL.Query(str.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables[0].Rows[i];
                    //添加一行数据
                    DataRow row = data.NewRow();
                    row[0] = Guid.Parse(dr["ID"].ToString());
                    row[1] = dr["OrganizationName"].ToString();
                    row[2] = int.Parse(dr["ClassLayer"].ToString());
                    row[3] = Guid.Parse(dr["ParentID"].ToString());
                    row[4] = dr["Description"].ToString();
                    row[5] = int.Parse(dr["Sort"].ToString());
                    row[6] = DateTime.Parse(dr["AddDate"].ToString());
                    data.Rows.Add(row);
                    //调用自身迭代
                    this.GetChild(data, Guid.Parse(dr["ID"].ToString()));
                }
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public SZSLSystem.Model.Organization GetParentModel(Guid Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from Organization ");
            strSql.Append(" where ParentId=@ParentId");
            SqlParameter[] parameters = {
					new SqlParameter("@ParentId", SqlDbType.UniqueIdentifier)
            };
            parameters[0].Value = Id;

            SZSLSystem.Model.Organization model = new SZSLSystem.Model.Organization();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow row = ds.Tables[0].Rows[0];
                if (row["ID"].ToString() != "")
                {
                    model.ID = (Guid)row["ID"];
                }
                model.OrganizationName = row["OrganizationName"].ToString();
                model.ClassLayer = int.Parse(row["ClassLayer"].ToString());
                if (row["ParentID"].ToString() != "")
                {
                    model.ParentID = (Guid)row["ParentID"];
                }
                model.Description = row["Description"].ToString();
                if (row["Sort"].ToString() != "")
                {
                    model.Sort = int.Parse(row["Sort"].ToString());
                }
                model.AddDate = DateTime.Parse(row["AddDate"].ToString());
                return model;
            }
            else
            {
                return null;
            }
        }

        public DataTable GetList(string fileName, string strWhere, string typeID)
        {
            StringBuilder str = new StringBuilder();
            str.Append("select * from Organization a");
            str.Append(" where " + fileName + "='" + strWhere + "'");
            str.Append(" order by a.Sort asc");
            DataTable ds = DbHelperSQL.Query(str.ToString()).Tables[0];
            return ds;
        }

        /// <summary>
        /// 获得管理员查询分页数据
        /// </summary>
        /// <param name="pageSize">每页数目</param>
        /// <param name="currentPage">当前页数</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="filedOrder">排序条件</param>
        /// <returns></returns>
        public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder)
        {
            int topSize = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top " + pageSize + " * from Organization");
            strSql.Append(" where Id not in(select top " + topSize + " Id from Organization");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder + ")");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);

            return DbHelperSQL.Query(strSql.ToString());
        }

        #endregion  Method
    }
}
